﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using HZ.Data;
using System.Data.SqlClient;
using HZ.DrugTrade.Model;

namespace HZ.DrugTrade.DAL
{
    public class DAL_Interface
    {
        public static IList<HZ.DrugTrade.Model.View.HospitalInterface> GetMatchedHospitalInterface(HZ.DrugTrade.Model.Query.HospitalInterface where, int CurrentPage, int PageSize, out int RecordCount)
        {

            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            PageDescribe des = new PageDescribe(string.Format("SELECT * FROM View_HospitalInterface WHERE 1=1{0}", strwhere), PageSize);
            des.CurrentPage = CurrentPage;
            RecordCount = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalInterface>(des.GetSpecailPage());
            //  
        }

        public static IList<HZ.DrugTrade.Model.View.HospitalProcureCatalog> GetUnMatchedHospitalInterface(HZ.DrugTrade.Model.Query.HospitalProcureCatalog where, int CurrentPage, int PageSize, out int RecordCount)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            PageDescribe des = new PageDescribe(string.Format("SELECT * FROM View_HospitalProcureCatalog WHERE ProcureCatalogID NOT IN (SELECT ProcureCatalogID FROM TB_HospitalInterfaceCorrelation WHERE HospitalID = '{0}') AND IsUsing = 1{1}", where.HospitalID, strwhere), PageSize);
            des.CurrentPage = CurrentPage;
            RecordCount = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalProcureCatalog>(des.GetSpecailPage());
        }
        /// <summary>
        /// 删除一个已对应接口
        /// </summary>
        /// <param name="HospitalInterfaceCorrelatio"></param>
        /// <returns></returns>
        public static int DeleteHospitalInterfaceCorrelation(HZ.DrugTrade.Model.HospitalInterfaceCorrelatio HospitalInterfaceCorrelation)
        {
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@in_HISDrugID", HospitalInterfaceCorrelation.HISDrugID),
                new SqlParameter("@out_success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;


            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_HospitalInterfaceCorrelation_Delete", paras);

            return (int)paras[paras.Length - 1].Value;
        }

        public static IList<HZ.DrugTrade.Model.View.HospitalProcureCatalog> QueryServerProcureCatalogCommon(HZ.DrugTrade.Model.Query.HospitalProcureCatalog HospitalProcureCatalog, int CurrentPage, int PageSize, out int TotalRecord)
        {
            //todo: QueryServerProcureCatalogCommon未完成！

            TotalRecord = 0;
            return null;
        }


        /// <summary>
        /// 智能查询
        /// </summary>
        /// <param name="HospitalProcureCatalog"></param>
        /// <param name="CurrentPage"></param>
        /// <param name="PageSize"></param>
        /// <param name="TotalRecord"></param>
        /// <param name="similarfactor"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalProcureCatalog> QueryServerProcureCatalogIntelligence(HZ.DrugTrade.Model.Query.HospitalProcureCatalog HospitalProcureCatalog, int CurrentPage, int PageSize, out int TotalRecord, double similarfactor)
        {
            StringBuilder sbwhere = new StringBuilder();
            sbwhere.AppendFormat(" AND GetSimilar(GoodsName, '{0}') >= {1}", HospitalProcureCatalog.GoodsName, similarfactor);

            if (!string.IsNullOrEmpty(HospitalProcureCatalog.Outlookc))
            {
                sbwhere.Append(Search_Util.GetOutlookc(HospitalProcureCatalog.Outlookc));
            }

            if (!string.IsNullOrEmpty(HospitalProcureCatalog.Pack))
            {
                sbwhere.Append(Search_Util.GetFactor(HospitalProcureCatalog.Pack));
            }


            if (!string.IsNullOrEmpty(HospitalProcureCatalog.Companyname_Sc))
            {
                sbwhere.Append(Search_Util.GetCompanyName_SC(HospitalProcureCatalog.Companyname_Sc));
            }

            string sql = string.Format("SELECT * FROM View_HospitalProcureCatalog WHERE HospitalID = '{0}' AND Hospital_IsUsing = 1 AND ProcureCatalogID NOT IN (SELECT ProcureCatalogID FROM TB_HospitalInterfaceCorrelation WHERE HospitalID = '{0}'){1} ORDER BY GetSimilar(GoodsName, '{2}') DESC", HospitalProcureCatalog.HospitalID, sbwhere.ToString(), HospitalProcureCatalog.GoodsName);
            PageDescribe des = new PageDescribe(sql, PageSize);



            des.CurrentPage = CurrentPage;
            TotalRecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalProcureCatalog>(des.GetSpecailPage());

            //  

        }

        public static int DeleteCompanyInterfaceCorrelation(HZ.DrugTrade.Model.CompanyInterfaceCorrelation CompanyInterfaceCorrelation)
        {
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@in_ERPDrugID", CompanyInterfaceCorrelation.ERPDrugID),
                new SqlParameter("@out_success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;


            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_CompanyInterfaceCorrelation_Delete", paras);

            return (int)paras[paras.Length - 1].Value;
        }

        public static IList<HZ.DrugTrade.Model.View.ProcureCatalog> QueryServerProcureCatalogIntelligence_Company(HZ.DrugTrade.Model.Query.ProcureCatalog where, int CurrentPage, int PageSize, out int TotalRecord, double similarfactor)
        {
            //todo:QueryServerProcureCatalogIntelligence_Company未完成！
            TotalRecord = 0;
            return null;
        }


        /// <summary>
        /// 获取对应的企业数据
        /// </summary>
        /// <param name="where"></param>
        /// <param name="CurrentPage"></param>
        /// <param name="PageSize"></param>
        /// <param name="RecordCount"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.CompanyInterface> GetMatchedCompanyInterface(HZ.DrugTrade.Model.Query.CompanyInterface where, int CurrentPage, int PageSize, out int RecordCount)
        {
            return HZ.DrugTrade.DAL2.DAL_Helper.GetPagedView<HZ.DrugTrade.Model.View.CompanyInterface, HZ.DrugTrade.Model.Query.CompanyInterface>(where, CurrentPage, PageSize, out RecordCount);
        }

        /// <summary>
        /// 对应采购单实现
        /// </summary>
        /// <param name="purchase"></param>
        /// <param name="AccountID"></param>
        /// <param name="UnMatchedPurchase"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.ImportedPurchaseOrderDetail> MatchPurchaseMemu(IList<HZ.DrugTrade.Model.ImportPurchaseOrderDetail> purchase, string AccountID, out IList<HZ.DrugTrade.Model.ImportPurchaseOrderDetail> UnMatchedPurchase)
        {
            string sql = string.Format(@"SELECT h.ProcureCatalogID,h.GoodsName,h.Outlookc, h.Factor,h.CompanyName_SC, h.CompanyName_TB, h.Price, h.CompanyName_PS, h.CompanyID_PS,h.ProjectName,h.FlowID, v.HISDrugID, 0 AS PurchaseCount FROM View_HospitalInterface AS v INNER JOIN View_DistributionRelation AS h ON h.ProcureCatalogID = v.ProcureCatalogID AND h.HospitalID = v.HospitalID WHERE v.HospitalID = '{0}' AND h.Hospital_IsUsing = 1 AND h.DistributionIsUsing = 1  AND  v.HISDrugID IN ({1})", AccountID, HZ.Data.DAL_Helper.GetModelFieldArrayString<HZ.DrugTrade.Model.ImportPurchaseOrderDetail>(purchase, "HisDrugID", ",", true));

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

            IList<HZ.DrugTrade.Model.View.ImportedPurchaseOrderDetail> list = null;
            list = HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.ImportedPurchaseOrderDetail>(dt);
            // 赋值采购数量

            IDictionary<string, HZ.DrugTrade.Model.ImportPurchaseOrderDetail> dicache = new Dictionary<string, HZ.DrugTrade.Model.ImportPurchaseOrderDetail>();

            foreach (var item in purchase)
            {
                dicache.Add(item.HisDrugID, item);
            }

            for (int i = 0; i < list.Count; i++)
            {
                list[i].PurchaseCount = dicache[list[i].HISDrugID].PurchaseCount;
            }


            foreach (var item in list)
            {
                HZ.DrugTrade.Model.ImportPurchaseOrderDetail temp = new HZ.DrugTrade.Model.ImportPurchaseOrderDetail() { HisDrugID = item.HISDrugID };
                HZ.Fun2.Remove(purchase, temp);
            }
            UnMatchedPurchase = purchase;
            return list;
        }
        /// <summary>
        /// 自动对应接口数据
        /// </summary>
        /// <param name="CorrelationList">客户端待对应数据列表</param>
        /// <param name="UnCorrelationList">未对应数据列表</param>
        /// <remarks>CorrelationList必须把当前医疗机构ID写入</remarks>
        /// <returns>1成功 0失败</returns>
        public static int AutoCorresponding
            (IList<HZ.DrugTrade.Model.HospitalInterfaceCorrelatio> CorrelationList, out IList<HZ.DrugTrade.Model.HospitalInterfaceCorrelatio> UnCorrelationList)
        {
            double similarfactor = 0.6;
            UnCorrelationList = new List<HZ.DrugTrade.Model.HospitalInterfaceCorrelatio>();
            foreach (var item in CorrelationList)
            {
                StringBuilder sbwhere = new StringBuilder();
                sbwhere.AppendFormat(" AND GetSimilar(GoodsName, '{0}') >= {1}", item.CProductName, similarfactor);

                if (!string.IsNullOrEmpty(item.CMedicineModel))
                {
                    sbwhere.Append(Search_Util.GetModelQuery(item.CMedicineModel));
                }

                if (!string.IsNullOrEmpty(item.COutlookc))
                {
                    sbwhere.Append(Search_Util.GetOutlookc(item.COutlookc));
                }

                if (!string.IsNullOrEmpty(item.CPack))
                {
                    sbwhere.Append(Search_Util.GetFactor(item.CPack));
                }


                if (!string.IsNullOrEmpty(item.CCompanyName_SC))
                {
                    sbwhere.Append(Search_Util.GetCompanyName_SC(item.CCompanyName_SC));
                }

                string sql = string.Format("SELECT * FROM View_HospitalProcureCatalog WHERE HospitalID = '{0}' AND Hospital_IsUsing = 1 AND ProcureCatalogID NOT IN (SELECT ProcureCatalogID FROM TB_HospitalInterfaceCorrelation WHERE HospitalID = '{0}'){1} ORDER BY GetSimilar(GoodsName, '{2}') DESC", item.HospitalID, sbwhere.ToString(), item.CProductName);
                DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

                if (dt.Rows.Count == 0)
                {
                    UnCorrelationList.Add(item);
                }
                else
                {
                    string ProcureCatalogID = dt.Rows[0]["ProcureCatalogID"].ToString();
                    item.ProcureCatalogID = ProcureCatalogID;
                    item.CorrelateTime = DateTime.Now;
                    HZ.Data.DAL_Helper.OperateTable(item, TableOperator.Insert);
                }
            }
            return 1;


        }

        public static int AutoCorresponding_Company(IList<HZ.DrugTrade.Model.CompanyInterfaceCorrelation> CorrelationList, out IList<HZ.DrugTrade.Model.CompanyInterfaceCorrelation> UnCorrelationList)
        {
            double similarfactor = 0.6;
            UnCorrelationList = new List<HZ.DrugTrade.Model.CompanyInterfaceCorrelation>();
            foreach (var item in CorrelationList)
            {
                StringBuilder sbwhere = new StringBuilder();
                sbwhere.AppendFormat(" AND GetSimilar(GoodsName, '{0}') >= {1}", item.CProductName, similarfactor);

                if (!string.IsNullOrEmpty(item.COutlookc))
                {
                    sbwhere.Append(Search_Util.GetOutlookc(item.COutlookc));
                }

                if (!string.IsNullOrEmpty(item.Cpack))
                {
                    sbwhere.Append(Search_Util.GetFactor(item.Cpack));
                }


                if (!string.IsNullOrEmpty(item.CCompanyName_SC))
                {
                    sbwhere.Append(Search_Util.GetCompanyName_SC(item.CCompanyName_SC));
                }

                string sql = string.Format("SELECT * FROM View_ProcureCatalog WHERE IsUsing = 1 AND ProcureCatalogID NOT IN (SELECT ProcureCatalogID FROM TB_CompanyInterfaceCorrelation WHERE CompanyID = '{0}'){1} ORDER BY GetSimilar(GoodsName, '{2}') DESC", item.CompanyID, sbwhere.ToString(), item.CProductName);
                DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

                if (dt.Rows.Count == 0)
                {
                    UnCorrelationList.Add(item);
                }
                else
                {
                    string ProcureCatalogID = dt.Rows[0]["ProcureCatalogID"].ToString();
                    item.ProcureCatalogID = ProcureCatalogID;
                    item.CorrelateTime = DateTime.Now;
                    HZ.Data.DAL_Helper.OperateTable(item, TableOperator.Insert);
                }
            }
            return 1;

        }





        //public static DataTable GetCorrespondingPurchaseOrderDetail_Company(HZ.DrugTrade.Model.Query.PurchaseOrderDetail where, int currentpage, int pagesize, out int totalrecord)
        //{
        //    string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);


        //    PageDescribe des = new PageDescribe(string.Format(@"SELECT c.ERPDrugID, c.CProductName, c.CMedicineModel, c.COutlookc , c.Cpack , c.CCompanyName_SC , CONVERT(FLOAT, v.PurchaseCount)*c.CFactor/v.Factor AS PurchaseCount, v.Price FROM TB_CompanyInterfaceCorrelation C INNER JOIN  View_PurchaseOrderDetail AS V ON c.ProcureCatalogID = v.ProcureCatalogID WHERE 1=1 {0}", strwhere), pagesize);
        //    des.CurrentPage = currentpage;
        //    totalrecord = des.TotalCount;
        //    return des.GetSpecailPage();
        //}

        public static IList<HZ.DrugTrade.Model.Interface.ERPDistribution> ImportDistribution(IList<HZ.DrugTrade.Model.Interface.ERPDistribution> erplist, HZ.DrugTrade.Model.PurchaseOrder PurchaseOrder, string companyID, out IList<HZ.DrugTrade.Model.Interface.ERPDistribution> UnMatchedERPDistribution)
        {

            string strerplist = HZ.Data.DAL_Helper.GetModelFieldArrayString(erplist, "ERPDrugID", ",", true);

            string sql = string.Format(@"SELECT A.* FROM TB_PurchaseOrderDetail AS A INNER JOIN  TB_CompanyInterfaceCorrelation AS B ON A.ProcureCatalogID = B.ProcureCatalogID WHERE B.ERPDrugID IN ({0}) AND A.OrderID = '{1}'", strerplist, PurchaseOrder.OrderID);
            IList<HZ.DrugTrade.Model.PurchaseOrderDetail> detailist = HZ.Data.DAL_Helper.CommonFillList<PurchaseOrderDetail>(HZ.Data.DbHelperFactory.Create().ExecuteTable(sql));
            UnMatchedERPDistribution = new List<HZ.DrugTrade.Model.Interface.ERPDistribution>();
            IList<HZ.DrugTrade.Model.Interface.ERPDistribution> result = new List<HZ.DrugTrade.Model.Interface.ERPDistribution>();
            // 获取ERP系统和系统字段对应表

            // 通过系统字段查ERP字段
            Dictionary<string, string> dic = new Dictionary<string, string>();

            sql = string.Format(@"SELECT ERPDrugID,ProcureCatalogID FROM TB_CompanyInterfaceCorrelation
WHERE CompanyID = '{0}'", companyID);

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            foreach (DataRow item in dt.Rows)
            {
                dic.Add(item["ProcureCatalogID"].ToString(), item["ERPDrugID"].ToString());
            }

            //ERP数据缓存
            Dictionary<string, Model.Interface.ERPDistribution> erpdic = new Dictionary<string, Model.Interface.ERPDistribution>();
            foreach (var item in erplist)
            {
                erpdic.Add(item.ERPDrugID, item);
            }

            if (detailist.Count > 0)
            {
                try
                {
                    List<Distribution> dislist = new List<Distribution>();
                    List<DistributionBatchRecord> disblist = new List<DistributionBatchRecord>();
                    foreach (var item in detailist)
                    {
                        Distribution dis = new Distribution();
                        dis.DistributionSerialID = Guid.NewGuid().ToString();
                        dis.OrderDetailID = item.OrderDetailID;
                        string erpid = dic[item.ProcureCatalogID];

                        dis.DistributeCount = erpdic[erpid].DistributeCount;
                        dis.DistributeTime = erpdic[erpid].DistributeTime;
                        dis.DistributeState = 0;
                        DistributionBatchRecord db = new DistributionBatchRecord();
                        db.DistributionSerialID = dis.DistributionSerialID;
                        db.BatchRecordID = erpdic[erpid].BatchRecordID;

                        dislist.Add(dis);
                        disblist.Add(db);
                        result.Add(erpdic[erpid]);

                    }
                    DAL_PurchaseOrderDetail.UpdatePurchaseOrderDetail_Distribution_Batch(dislist, disblist);
                }
                catch (Exception ee)
                {
                    HZ.Common.log4netManager.LogDebug("DAL_Interface", ee.Message, ee);

                    throw ee;
                }

            }
            foreach (var item in erplist)
            {
                if (!result.Contains(item))
                {
                    UnMatchedERPDistribution.Add(item);
                }
            }

            //todo: ImportDistribution未完成！
            return result;
        }




        public static IList<HZ.DrugTrade.Model.View.CompanyProcureCatalog> GetUnMatchedCompanyProcureCatalog(HZ.DrugTrade.Model.Query.CompanyProcureCatalog where, int CurrentPage, int PageSize, out int TotalRecord)
        {
            string sql = string.Format("SELECT * FROM View_CompanyProcureCatalog WHERE ProcureCatalogID NOT IN (SELECT ProcureCatalogID FROM TB_CompanyInterfaceCorrelation WHERE CompanyID = '{0}'){1}", where.CompanyID, HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true));
            PageDescribe des = new PageDescribe(sql, PageSize);
            des.CurrentPage = CurrentPage;
            TotalRecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.CompanyProcureCatalog>(des.GetSpecailPage());
            throw new NotImplementedException();
        }
    }
}
